﻿using Dapper;
using Gx.Common.Core;
using Gx.Model.ViewModel;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Gx.Dal.Manage
{
    public class LKCustomerReportDal
    {
        /// <summary>
        /// 客户存货汇总报表
        /// </summary>
        /// <param name="input"></param>
        /// <returns></returns>
        public List<CustomerInventorySummaryDto> GetCustomerInventorySummaryReport(CustomerInventorySummaryInput input)
        {
            if (input.WarehouseId.HasValue && input.WarehouseId != 0)
            {
                var wareHouseDal = new LKWareHouseDal();
                input.WarehouseIds = wareHouseDal.GetWareHouseByParentId(input.WarehouseId.Value);
            }
            using (var conn = new DbContext())
            {
                List<string> econditions = new List<string>();
                List<string> oconditions = new List<string>();
                if (input.SummaryTime.HasValue)
                {
                    econditions.Add("EntryTime<=@SummaryTime");
                    oconditions.Add("OutTime<=@SummaryTime");
                }
                if (input.WarehouseIds.Any())
                {
                    econditions.Add("WareHouseId in @WarehouseIds");
                    oconditions.Add("WarehouseId in @WarehouseIds");
                }
                else if (input.WarehouseId.HasValue && input.WarehouseId != 0)
                {
                    econditions.Add("WareHouseId=@WarehouseId");
                    oconditions.Add("WarehouseId=@WarehouseId");
                }

                string sql = @"SELECT we.CustomerId, we.CustomerNo,we.CustomerName,
we.total-ISNULL(wo.Total,0) AS Total,we.TotalTon-ISNULL(wo.TotalTon,0) AS TotalTon
FROM(
SELECT CustomerId, CustomerNo,CustomerName,SUM(Total) AS Total,SUM(TotalTon) AS TotalTon FROM dbo.LK_WarehouseEntry e WITH(NOLOCK)
JOIN dbo.LK_WarehouseEntryDetail ed WITH(NOLOCK) ON e.Id=ed.EntryId 
WHERE e.IsDeleted=0 AND ed.IsDeleted=0 {0}
GROUP BY CustomerId, CustomerNo,CustomerName) AS we
LEFT JOIN
(SELECT CustomerId, CustomerNo,CustomerName,SUM(Total) AS Total,SUM(TotalTon) AS TotalTon FROM dbo.LK_WarehouseOut o WITH(NOLOCK)
JOIN dbo.LK_WarehouseOutDetail od WITH(NOLOCK) ON o.id=od.OutId
WHERE o.IsDeleted=0 AND od.IsDeleted=0 {1}
GROUP BY CustomerId, CustomerNo,CustomerName) AS wo ON we.CustomerId=wo.CustomerId";

                string econdition = string.Empty;
                if (econditions.Count > 0)
                {
                    econdition = " AND " + string.Join(" AND ", econditions);
                }
                string ocondition = string.Empty;
                if (oconditions.Count > 0)
                {
                    ocondition = " AND " + string.Join(" AND ", oconditions);
                }
                sql = string.Format(sql, econdition, ocondition);
                return conn.Connection.Query<CustomerInventorySummaryDto>(sql, input).ToList();
            }
        }

        /// <summary>
        /// 客户存货明细报表
        /// </summary>
        /// <param name="input"></param>
        /// <returns></returns>
        public List<CustomerInventoryDetailDto> GetCustomerInventoryDetailReport(CustomerInventoryDetailInput input)
        {
            if (input.WarehouseId.HasValue && input.WarehouseId != 0)
            {
                var wareHouseDal = new LKWareHouseDal();
                input.WarehouseIds = wareHouseDal.GetWareHouseByParentId(input.WarehouseId.Value);
            }
            using (var conn = new DbContext())
            {
                List<string> econditions = new List<string>();
                List<string> oconditions = new List<string>();
                if (input.SummaryTime.HasValue)
                {
                    econditions.Add("EntryTime<=@SummaryTime");
                    oconditions.Add("OutTime<=@SummaryTime");
                }
                if (input.CustomerId.HasValue)
                {
                    econditions.Add("CustomerId=@CustomerId");
                    oconditions.Add("CustomerId=@CustomerId");
                }
                if (input.WarehouseIds.Any())
                {
                    econditions.Add("WareHouseId in @WarehouseIds");
                    oconditions.Add("WarehouseId in @WarehouseIds");
                }
                else if (input.WarehouseId.HasValue)
                {
                    econditions.Add("WareHouseId=@WarehouseId");
                    oconditions.Add("WarehouseId=@WarehouseId");
                }
                if (!string.IsNullOrEmpty(input.GoodsCode))
                {
                    econditions.Add("GoodsNo=@GoodsCode");
                    oconditions.Add("GoodsNo=@GoodsCode");
                }
                if (!string.IsNullOrEmpty(input.GoodsName))
                {
                    econditions.Add("GoodsName=@GoodsName");
                    oconditions.Add("GoodsName=@GoodsName");
                }
                if (!string.IsNullOrEmpty(input.GoodsSpec))
                {
                    econditions.Add("GoodsSpec=@GoodsSpec");
                    oconditions.Add("GoodsSpec=@GoodsSpec");
                }
                if (!string.IsNullOrEmpty(input.ContainerNumber))
                {
                    econditions.Add("ContainerNumber=@ContainerNumber");
                    //oconditions.Add("ContainerNumber=@ContainerNumber");
                }
                if (input.RentalWarehouseType.HasValue)
                {
                    econditions.Add("RentalWarehouseType=@RentalWarehouseType");
                }
                string sql = @"SELECT e.CustomerNo,
       e.CustomerName,
ed.GoodsNo,
       ed.GoodsName,
       ed.GoodsSpecName,
       ed.WareHouseName,
       e.ContainerNumber,
CONVERT(varchar, ed.ProductionDate, 23) AS ProductionDate,
       CONVERT(varchar, ed.ExpirationDate, 23) AS ExpirationDate,
       ed.ProducingArea,
       ed.Remark,
       ed.Weight,
       ed.Number - ISNULL(o.OutNumber, 0) AS Total,
       ed.Area,
       ed.TonWeight - ISNULL(o.TonWeight, 0) AS TotalTon,
       CONVERT(varchar, e.EntryTime, 23) AS EntryTime,
       CASE e.RentalWarehouseType
           WHEN 1 THEN
               '零仓'
           WHEN 2 THEN
               '包仓'
       END AS RentalWarehouseTypeName
FROM dbo.LK_WarehouseEntryDetail ed WITH (NOLOCK)
    JOIN dbo.LK_WarehouseEntry e WITH (NOLOCK)
        ON ed.EntryId = e.Id
    LEFT JOIN
    (
        SELECT od.GoodsNo,
               SUM(od.OutNumber) AS OutNumber,
               SUM(od.TonWeight) TonWeight
        FROM dbo.LK_WarehouseOutDetail od WITH (NOLOCK)
JOIN dbo.LK_WarehouseOut o WITH(NOLOCK) ON od.OutId=o.Id
        WHERE od.IsDeleted = 0 {1}
        GROUP BY od.GoodsNo
    ) AS o
        ON ed.GoodsNo = o.GoodsNo
WHERE ed.IsDeleted = 0
      AND e.IsDeleted = 0 {0}";
                string econdition = string.Empty;
                if (econditions.Count > 0)
                {
                    econdition = " AND " + string.Join(" AND ", econditions);
                }
                string ocondition = string.Empty;
                if (oconditions.Count > 0)
                {
                    ocondition = " AND " + string.Join(" AND ", oconditions);
                }
                sql = string.Format(sql, econdition, ocondition);
                return conn.Connection.Query<CustomerInventoryDetailDto>(sql, input).ToList();
            }
        }
    }
}
